package com.yuda.tools.checkanswer.mapper;

import com.yuda.tools.checkanswer.dto.*;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface CheckAnswerMapper {


    @MapKey("subjectId")
    @Select(value = "SELECT subject_id as subjectId, num as c from ques_count")
    Map<Integer, Map<Integer, Integer>> getSubjectQuesCount();

    List<StudentOssDto> listStudentsForCheck(@Param("testSession") int testSession, @Param("subjectIndex") int subjectIndex);

    @Update("update sparta_exam_subject_student set answer_status = 2 , error_message = #{message} where student_id = #{studentId} and test_session = #{testSession} and subject_index = #{subjectIndex}")
    void updateAnswerStatus(@Param("studentId") String studentId, @Param("testSession") int testSession, @Param("subjectIndex") int subjectIndex, @Param("message") String message);

    @Update("update sparta_exam_subject_student set answer_status = 1,answer_db_type = 1,real_answer_area = #{realAnswerArea} , real_answer_name = #{realAnswerName} , error_message = #{message} where student_id = #{studentId} and test_session = #{testSession} and subject_index = #{subjectIndex}")
    void updateAnswerStatusOk(@Param("realAnswerArea") String realAnswerArea, @Param("realAnswerName") String realAnswerName, @Param("studentId") String studentId, @Param("testSession") int testSession, @Param("subjectIndex") int subjectIndex, @Param("message") String message);

    List<ResultMessage> listResultMessage(@Param("searchKey") String searchKey, @Param("provinceIds") Integer[] provinceIds, @Param("marked") Integer marked, @Param("handle") Integer handle);

    @Select("SELECT b.id,b.name from sparta_exam_student a,sparta_province b where a.province_id = b.id GROUP BY b.id order BY b.id")
    List<Province> listProvince();

    @Update("update sparta_exam_subject_student set answer_db_type = 1,download_type = 1,answer_status = 1, answer_source =#{status} where student_id = #{studentId} and subject_id=#{subjectId}")
    int updateAnswerSource(@Param("studentId") String studentId, @Param("subjectId") String subjectId, @Param("status") int status);

    @Select("SELECT num from ques_count where subject_id = #{subjectId}")
    Integer searchRealCount(String subjectId);

    @Select("select count(1) from mq_answer where student_id = #{studentId} and subject_id =#{subjectId} GROUP BY ques_id")
    Integer searchMqCount(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    @Select("select ques_id as quesId,answer from (" +
            "SELECT ques_id,answer from mq_answer where student_id = #{studentId} and subject_id = #{subjectId} and type =2 ORDER BY create_time desc  limit 100000000000" +
            ") t GROUP BY ques_id ORDER BY ques_id")
    List<MqAnswer> searchMqAnswer(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    //标记正常
    @Update("update sparta_exam_subject_student set answer_db_type = 1,download_type = 1,answer_status = 1,answer_source = 1, marked=1 where student_id = #{studentId} and subject_id = #{subjectId}")
    int markAsNormal(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    //取消标记
    @Update("update sparta_exam_subject_student set answer_db_type = 0,download_type = 0,answer_status = 2,answer_source = 1, marked=0 where student_id = #{studentId} and subject_id = #{subjectId}")
    int markAsAbnormal(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    @Select("select paper_id from sparta_paper_subject where subject_id =#{subjectId}")
    int getPaperIdBySubjectId(String subjectId);

    @Update("DELETE from tsinghua_formal_answer_marked where student_id = #{studentId} and subject_id = #{subjectId}")
    int deleteFormalAnswerMarked(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    @Update("insert into tsinghua_formal_answer_marked (student_id,subject_id,subject_relation_id,ques_id,paper_id,answer,answer_json,md5,type) " +
            " select student_id,subject_id,subject_relation_id,ques_id,paper_id,answer,answer_json,md5,ques_type from ( " +
            "            SELECT student_id,subject_id,subject_relation_id,ques_id,paper_id,answer,answer_json,md5,ques_type from mq_answer  " +
            "  where student_id = #{studentId} and subject_id =#{subjectId} and type =2  ORDER BY create_time desc  limit 100000000000 " +
            "            ) t GROUP BY ques_id ORDER BY ques_id")
    void moveAnswerFromMq(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    @Select("select marked from sparta_exam_subject_student where student_id = #{studentId} and subject_id = #{subjectId}")
    int isMarked(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    //换机位的次数
    @Select("SELECT count(1) from sparta_change_student where student_id = #{studentId} and paper_id = #{paperId}")
    Integer changePcNoCountByPaperId(@Param("studentId") String studentId, @Param("paperId") Integer paperId);

    //换机位的次数
    @Select("SELECT count(1) from sparta_change_student where student_id = #{studentId} and subject_id = #{subjectId}")
    Integer changePcNoCountBySubjectId(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    //为啥加 e_status 为0  ， 防止sparta_change_student 无记录，但是已经换了考位
    @Select("select pc_no from sparta_exam_student where student_id = #{studentId} and e_status =0")
    int getPcNo(String studentId);

    @Select("select concat(test_session,subject_index) tssi,GROUP_CONCAT(`name` separator '和') as subjectName from sparta_paper_subject_relation GROUP BY test_session,subject_index")
    List<SubjectMap> listSubjectTssi();

    List<ResultMessage> listResultExamIng(@Param("searchKey") String searchKey, @Param("provinceIds") Integer[] provinceIds);

    @Select("select count(1) from mq_answer where student_id = #{studentId} and subject_id = #{subjectId}")
    int checkMqExistStudent(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    @Select("select count(1)\n" +
            " from sparta_exam_subject_student ss LEFT JOIN sparta_exam_student es\n" +
            "on ss.student_id = es.student_id where ss.test_session = #{currentTestSession} and ss.subject_index =#{currentSubjectIndex} " +
            "and es.student_id !='' and es.student_id is not null and es.e_status != 1\n" +
            "and  ss.stu_start_time >0 and es.student_id !='000000' and es.name !='test' and es.computerroom_id !=1280")
    int getTotalTask(@Param("currentTestSession") Integer currentTestSession, @Param("currentSubjectIndex") Integer currentSubjectIndex);

    @Select(" select count(1)\n" +
            " from sparta_exam_subject_student ss LEFT JOIN sparta_exam_student es\n" +
            "on ss.student_id = es.student_id where ss.test_session = #{currentTestSession} and ss.subject_index = #{currentSubjectIndex} " +
            "and es.student_id !='' and es.student_id is not null and es.e_status != 1\n" +
            "and ss.stu_start_time >0 and answer_db_type = 1 and es.student_id !='000000' and es.name !='test' and es.computerroom_id !=1280")
    int getCheckedNum(@Param("currentTestSession") Integer currentTestSession, @Param("currentSubjectIndex") Integer currentSubjectIndex);

    @Update("update sparta_paper_subject_relation set checkanswer_success = 1 where test_session=#{currentTestSession} and subject_index = #{currentSubjectIndex}")
    void updateCheckedSuccess(@Param("currentTestSession") Integer currentTestSession, @Param("currentSubjectIndex") Integer currentSubjectIndex);

    @Select("SELECT if(count(id)= (select count(id) from sparta_paper_subject_relation),'y','n') success_status  from sparta_paper_subject_relation where checkanswer_success = 1")
    String checkCanBeUpdateRoadMap();

    @Update("update road_map set check_answer = 1")
    void updateRoadMap();

    @Select("select check_answer as checkAnswer,download_answer as downloadAnswer,merge_answer as mergeAnswer,import_score as importScore,qiongju,valid_answer as validAnswer,calculate_score as calculateScore,check_score checkScore,download_score downloadScore,tiankong from road_map limit 1")
    RoadMap roadMap();

    @Select("select ques_id quesId from tsinghua_formal_answer_marked where student_id = #{studentId} and subject_relation_id = #{subjectId}  GROUP BY ques_id")
    List<Integer> listQuestionIdsFromMarked(@Param("studentId") String studentId, @Param("subjectId") String subjectId);

    @Select("select ques_id quesId from sparta_paper_question where subject_relation_id = #{subjectId}")
    List<Integer> listAllQuestionsBySubjectId(String subjectId);

    void supplementAnswerToMarkedTable(@Param("list") List<FormalAnswerMarked> list);

    @Select("SELECT subject_id subjectId,paper_id paperId from sparta_paper_subject_relation where relation_subject_id = #{subjectRelationtId}")
    SubjectIdAndPaperId findSubjectIdAndPaperId(String subjectRelationtId);

    @Select("select zkz_num  zkzNum from sparta_exam_student where student_id = #{studentId} group by zkz_num")
    String getZkzNumByStudentId(String studentId);

    @Update("update tsinghua_formal_answer_marked a, sparta_paper_question b set a.type = b.type where a.subject_id = b.subject_id and a.ques_id = b.ques_id")
    void updateTypeForMarkedTable();

}
